VLOOKUP関数でリスト検索と値取得

元データの確認とシートの追加・列の挿入

まずは取り込む元データを確認します。

1: 元データの確認

依頼した通り、重要工程である熱処理工程の炉のNo.と処理時間を記録してくれました。しかし、流動した製品を全て記録したようで、これではどれが量産試作のロットなのかが分かりません。

そこで生産管理システムから量産試作データを取り出しました。

取り出したデータは、ロットNo.、品番、流動完了時刻、製品チップ数、良品数、不良品数、歩留まり となります。

2: シートの追加

この２つのデータをシートとして作成中のブックへ追加します。

現場の記入したデータを"熱処理工程リスト"、生産管理システムから取り出したデータを"量産試作データ"としてシート追加しました。

3: 検索列の挿入

"量産試作データ"シートのC～F列へ列を挿入し、見出し行の１行目へ取り込む項目名 熱処理炉No. 、 熱処理開始時刻 、 熱処理完了時刻 、処理チップ数 を入力します。

VLOOKUP関数でリストを検索

では、実際にVLOOKUP関数で"量産試作データ"側から"熱処理工程リスト"を検索していきます。VLOOKUP関数は ( 検索値, 範囲, 列番号, [検索方法] ) の４項目を設定します。

1: 検索値 の設定

検索は両方のシートに共通し重複のない項目で行います。そこで今回はロットNo.を 検索値 として設定します。"量産試作データ"シートのC2セルを選択し、 =VLOOKUP() と入力します。

カーソルが =VLOOKUP() のカッコの中にある状態で、A2セルを選択して 検索値 を設定し、項目区切りの " , " を入力して確定させます。

2: 範囲 の設定

続いて検索する 範囲 の設定です。VLOOKUP関数は範囲の左端列のみを検索しますので、範囲は検索する列（今回はロットNo.の列）を起点に右方向へ設定して下さい。

広い範囲の選択も [Shift]キー を組み合わせたショートカットキーで簡単にできます。まず、検索する"熱処理工程リスト"シートに切り替え、A2セルからE2セルまでをマウスで選択状態にします。その状態で [Shift]キー と [Ctrl]キーを押しながら、[↓]キーを押します。

これで 範囲 の設定が出来ました。項目区切りの " , " を入力して確定させます。ショートカットキー【 [Shift]キー と [Ctrl]キー と 任意の矢印キー 】は選んでいるセルから指定方向の入力済み最終セルまで選択移動します。

3: 列番号 と 検索方法 の設定

次に取得する値を 列番号 で指定します。

列番号 は 範囲 の左端から順に1, 2, 3… となるので取得したい 列番号 を設定します。C2セルは 熱処理炉No. を表示しますので、2 を入れ、 " , " を入力して確定させます。

なお、データ取り出し列が範囲の外にあると参照エラー #REF! となります。列番号で指定するデータ取り出し列まで 範囲 に含める様にして下さい。

最後の項目 検索方法 には FALSE の完全一致を必ず設定します。理由は後ほど解説します。

4: 絶対値指定へ変更

次に、このままオートフィルで残りのセルを自動入力すると、検索する 範囲 が順にずれて値が取得できませんので、一部を絶対値指定へ変更します。

入力されている 検索値 の A2 をマウスでクリックし、選択状態にします。そして [F4]キー を３回押してA列のみ絶対指定に変更します。

次に、範囲の A1 と D2831 を順にマウスでクリックして選択状態にし、 [F4]キー を１回押して絶対指定にします。[Enter]キー を押して、数式を確定させます。

これでC2セルには、取得した ロットNo.AD0001 の 熱処理炉No. 1 が表示されました。

5: 値が見つからない時の不具合

D2～F2セルにも同様に入力し、取得する値の列番号のみを１つずつズラします。（D2セルは3、E2セルは4、F2セルは5）そして、オートフィルで各列末尾まで自動入力しました。

しかし、試しに F列の 処理チップ数 の合計数を計算させると…

なぜか エラー #N/A になってしまいます。

原因を調べると、エラー #N/A となっているロットがいくつかありました。

どうやら"熱処理工程リスト"シートにないロットNo.だったため、エラーとなったようです。現場に聞いたところ、これらのロットは品管の指示で立ち上げ検証ロットとして量産寄与前の熱処理炉No.4で処理したとのこと。そのため、依頼した量産試作の記録には残らなかった様です。

VLOOKUP関数の完全一致検索で値が見つからない時はエラー #N/A となります。問題は他の数式が参照していると、参照している数式側もエラー #N/A となってしまう点です。

6: IFERROR関数の追加

そこで、IFERROR関数を使い、VLOOKUP関数がエラーとなった時に代わりの値を返します。

C2セルの数式を =IFERROR(VLOOKUP($A2,熱処理工程リスト!$A$2:$E$2831,2,FALSE),0) へ変更し、数式を完成させます。この式はVLOOKUP関数がエラーとなった時に 0 を返す設定です。D2～F2セルも同様に入力し、オートフィルで各列末尾まで自動入力します。

これで、先ほどまでデータが見つからずエラーとなっていたロットの行も 0 が入力されています。

F列の 処理チップ数 の合計数も計算できています。VLOOKUP関数を使ったリスト検索と値取得の数式が完成しました。

これで、"量産試作データ"側に"熱処理工程リスト"のデータを取り込むことが出来ました。

おまけ：VLOOKUP関数で完全一致の FALSE が必須な理由

VLOOKUP関数の４つ目の設定項目　[検索方法]　は、近似一致の TRUE と、完全一致の FALSE から選択します。省略は可能ですが、その場合は標準設定の近似一致の TRUE　となります。

1: 近似一致の TRUE で検索させると・・・

試しに 熱処理開始時刻 を近似一致の TRUE で検索させてみます。

完全一致の FALSE で検索した時と一部のロットで開始時刻が変わっていることが分かります。

2: 近似一致の誤った検索結果

そこで検索結果を"熱処理工程リスト"シートへ追加し、確認してみます。

ロットNo. AD0012 に注目すると、本来の AD0012の開始時刻ではなく名前が似た AD0010 の開始時刻を参照していることが分かります。この様に近似一致を使うと正しい検索対象がいても別の近似セルの結果を返すことがあります。

近似一致は、検索対象を昇順にソートし、適切な近似値が得られるようにリスト間隔を設定するなど、事前準備をしないとうまく動きません。ロットNo.などの文字列は、近似値を探す必要がありませんので、完全一致の FALSE で検索します。